package JDBC_1.Blob;

import JDBC_1.util.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @author ：Yan Guang
 * @date ：Created in 2020/5/14 15:57
 * @description： 使用PreparedStatement实现批量数据的操作
 * update、delete本身就具有批量操作的效果
 * insert可以演示批量的效果
 *
 * 方式一：statement
 * 题目:向goods表中插入20000条数据
 * CREATE TABLE goods(
 * id INT PRIMARY KEY AUTO_ INCREMENT ,
 * NAME VARCHAR(25)
 * );
 * 方式一:使用Statement
 * Connection conn = JDBCUtils. getConnection();
 * Statement st = conn. createStatement( ) ;
 * for(int i = 1;i <= 20000;i++){
     * String sql = "insert into goods (name)values('name_"+i+"')";
     * st. execute(sq1);
 * }
 */
public class InsertTest {
    //方式二：
    @Test
    public void test2(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql="insert into goods (name)values(?)";//sql语句放在外面，节省空间，而且速率快一些
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 2000; i++) {
                ps.setObject(1,"name_"+i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为： "+(end-start));//花了44秒
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResourece(connection,ps);
        }
    }
    /*
    批量操作方式三：
    1.addBatch\executeBatch()\clearBatch()
     */
    @Test
    public void test3(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql="insert into goods (name)values(?)";//sql语句放在外面，节省空间，而且速率快一些
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 2000; i++) {
                ps.setObject(1,"name_"+i);

                //1.攒SQL进Batch
                ps.addBatch();
                if (i%500==0){
                    //2.执行Batch
                    ps.executeBatch();
                    //3.清空Batch
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为： "+(end-start));//花了0.3秒
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResourece(connection,ps);
        }
    }
    //终极方案：设置不允许自动提交数据
    @Test
    public void test4(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();

            //设置不允许自动提交数据
            connection.setAutoCommit(false);
            String sql="insert into goods (name)values(?)";//sql语句放在外面，节省空间，而且速率快一些
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 200000; i++) {
                ps.setObject(1,"name_"+i);

                //1.攒SQL进Batch
                ps.addBatch();
                if (i%500==0){
                    //2.执行Batch
                    ps.executeBatch();
                    //3.清空Batch
                    ps.clearBatch();
                }
            }
            connection.commit();

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为： "+(end-start));//花了0.3秒
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResourece(connection,ps);
        }
    }
}
